﻿using DB;
using Model.Base;
using Model.Extend;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace IDAL
{
    public class BaseModelADO<T> : BaseADO<T>, IBaseModelADO<T>, IExDelADO<T> where T : BaseModel, new()
    {
        #region 辅助方法
        private static string PKName = ModelAttribute.GetPKName<T>();
        #endregion

        #region 增删改
        /// <summary>
        /// 新增一条数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public long Add(T m, params string[] removeField)
        {
            m.CreateTime = DateTime.Now;
            m.IsDel = 2;
            m.LastModifyTime = null;
            string sql = ADOHelper.GetAddStr(m, TableName, PKName, p => p.Name.GetFieldAttribute<T>(false).Own, removeField);
            DbParameter[] ps = ADOHelper.SetParmValues(m, false);
            m.ID = db.ExecuteScalarText<long>(sql, ps);
            return m.ID;
        }

        #region 删除
        /// <summary>
        /// 根据主键id删除一条数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete(int id)
        {
            string sql = string.Format(@"DELETE FROM [{0}] WHERE [{1}]=@{1}", TableName, PKName);
            DbParameter[] ps = new DbParameter[] { DBHelpers.CreateParameter(PKName, id) };
            return db.ExecteNonQueryText(sql, ps) > 0;
        }
        /// <summary>
        /// 根据主键id删除一条数据并返回指定字段数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <param name="resStr">传入字段名称返回字段数据</param>
        /// <returns></returns>
        public bool Delete(int id, ref string resStr)
        {
            string sql = string.Format(@"DELETE FROM [{0}] OUTPUT ISNULL(DELETED.[{2}],'') WHERE [{1}]=@{1}", TableName, PKName, resStr);
            DbParameter[] ps = new DbParameter[] { DBHelpers.CreateParameter(PKName, id) };
            object val = db.ExecuteScalarText<object>(sql, ps);
            if (val != null && val != DBNull.Value)
            {
                resStr = val.ToString();
                return true;
            }
            return false;
        }
        /// <summary>
        /// 根据主键id逻辑删除一条数据(伪删除,需要传递删除标志字段以及相关值和需要同时修改的字段和字段值)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <param name="listUpdateField">同步更新的字段及相关值</param>
        /// <returns></returns>
        public bool Delete(int id, Dictionary<string, object> listUpdateField)
        {
            DbParameter[] ps = new DbParameter[listUpdateField.Count + 1];
            ps[0] = DBHelpers.CreateParameter(PKName, id);

            int i = 0;
            StringBuilder updateSql = new StringBuilder();
            foreach (KeyValuePair<string, object> UpdateField in listUpdateField)
            {
                i++;
                ps[i] = DBHelpers.CreateParameter(UpdateField.Key, UpdateField.Value);
                if (i == 1)
                    updateSql.AppendFormat("[{0}]=@{0}", UpdateField.Key);
                else
                    updateSql.AppendFormat(",[{0}]=@{0}", UpdateField.Key);
            }
            string sql = string.Format("UPDATE [{0}] SET {2} WHERE [{1}]=@{1}", TableName, PKName, updateSql.ToString());
            return db.ExecteNonQueryText(sql, ps) > 0;
        }
        /// <summary>
        /// 根据主键id逻辑删除一条数据(伪删除,需要传递删除标志字段以及相关值和需要同时修改的字段和字段值)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <param name="listUpdateField">同步更新的字段及相关值</param>
        /// <param name="m">包含更新字段值的实体类</param>
        /// <param name="resStr">传入字段名称返回字段数据</param>
        /// <returns></returns>
        public bool Delete(int id, Dictionary<string, object> listUpdateField, ref string resStr)
        {
            DbParameter[] ps = new DbParameter[listUpdateField.Count + 1];
            ps[0] = DBHelpers.CreateParameter(PKName, id);

            int i = 0;
            StringBuilder updateSql = new StringBuilder();
            foreach (KeyValuePair<string, object> UpdateField in listUpdateField)
            {
                i++;
                ps[i] = DBHelpers.CreateParameter(UpdateField.Key, UpdateField.Value);
                if (i == 1)
                    updateSql.AppendFormat("[{0}]=@{0}", UpdateField.Key);
                else
                    updateSql.AppendFormat(",[{0}]=@{0}", UpdateField.Key);
            }
            string sql = string.Format("UPDATE [{0}] SET {3} OUTPUT ISNULL(DELETED.[{2}],'') WHERE [{1}]=@{1}", TableName, PKName, resStr, updateSql.ToString());
            object val = db.ExecuteScalarText<object>(sql, ps);
            if (val != null && val != DBNull.Value)
            {
                resStr = val.ToString();
                return true;
            }
            return false;
        }
        /// <summary>
        /// 根据主键id逻辑删除一条数据(伪删除,需要传递删除标志字段以及相关值和需要同时修改的字段和字段值)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <param name="listUpdateField">同步更新的字段及相关值</param>
        /// <param name="m">包含更新字段值的实体类</param>
        /// <returns></returns>
        public bool Delete(int id, IList<string> listUpdateField, T m)
        {
            Dictionary<string, object> listKV = GetKeyValue(listUpdateField, m);
            return Delete(id, listKV);
        }
        /// <summary>
        /// 根据主键id逻辑删除一条数据(伪删除,需要传递删除标志字段以及相关值和需要同时修改的字段和字段值)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <param name="listUpdateField">同步更新的字段及相关值</param>
        /// <param name="m">包含更新字段值的实体类</param>
        /// <param name="resStr">传入字段名称返回字段数据</param>
        /// <returns></returns>
        public bool Delete(int id, IList<string> listUpdateField, T m, ref string resStr)
        {
            Dictionary<string, object> listKV = GetKeyValue(listUpdateField, m);
            return Delete(id, listKV, ref resStr);
        }
        /// <summary>
        /// 根据自定义条件逻辑删除一条数据(伪删除,需要传递删除标志字段以及相关值和需要同时修改的字段和字段值)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strWhere"></param>
        /// <param name="paramFields"></param>
        /// <param name="listUpdateField">同步更新的字段及相关值</param>
        /// <returns></returns>
        public bool Delete(string strWhere, Dictionary<string, object> paramFields, Dictionary<string, object> listUpdateField)
        {
            //检查是否有相同名字的字段
            if (paramFields.Count(k => listUpdateField.ContainsKey(k.Key)) > 0)
            {
                throw new Exception("两个列表字段名不能重复");
            }
            DbParameter[] paramPS = ADOHelper.ToArrSqlParam(paramFields);

            DbParameter[] ps = new DbParameter[listUpdateField.Count];

            int i = 0;
            StringBuilder updateSql = new StringBuilder();
            foreach (KeyValuePair<string, object> UpdateField in listUpdateField)
            {
                ps[i] = DBHelpers.CreateParameter(UpdateField.Key, UpdateField.Value);
                if (i == 0)
                    updateSql.AppendFormat("[{0}]=@{0}", UpdateField.Key);
                else
                    updateSql.AppendFormat(",[{0}]=@{0}", UpdateField.Key);
                i++;
            }
            if (string.IsNullOrWhiteSpace(strWhere))
            {
                throw new Exception("条件不能为空");
            }
            else
            {
                if (strWhere.Trim().Substring(0, 3).ToLower() != "and")
                    strWhere = " AND " + strWhere;
            }
            string sql = string.Format("UPDATE [{0}] SET {2} WHERE 1=1 {1}", TableName, strWhere, updateSql.ToString());

            return db.ExecteNonQueryText(sql, ps.Concat(paramPS).ToArray()) > 0;
        }
        #endregion
        /// <summary>
        /// 根据主键Id更新一条记录（不指定需要更新的字段时默认更新所有字段）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="m"></param>
        /// <param name="updateField"></param>
        /// <returns></returns>
        public bool Update(T m, params string[] updateField)
        {
            string sql = ADOHelper.GetUpdateStr(m, TableName, PKName, p => p.Name.GetFieldAttribute<T>(false).Own, updateField);
            DbParameter[] ps = ADOHelper.SetParmValues(m, true);
            return db.ExecteNonQueryText(sql, ps) > 0;
        }
        /// <summary>
        /// 根据自定义条件更新一条记录（参数集合名称不能与实体属性名称相同）（不指定需要更新的字段时默认更新所有字段）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="m"></param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="paramFields">参数集合</param>
        /// <param name="updateField">需要更新的字段</param>
        /// <returns></returns>
        public bool Update(T m, string strWhere, Dictionary<string, object> paramFields, params string[] updateField)
        {
            DbParameter[] ps = ADOHelper.SetParmValues(m, true);
            //检查是否有相同名字的字段
            if (ps.Count(p => paramFields.ContainsKey(p.ParameterName)) > 0)
            {
                throw new Exception("两个列表字段名不能重复");
            }
            DbParameter[] paramPS = ADOHelper.ToArrSqlParam(paramFields);

            string sql = ADOHelper.GetUpdateStr(m, ModelAttribute.GetTableName<T>(), ModelAttribute.GetPKName<T>(), strWhere, p => p.Name.GetFieldAttribute<T>(false).Own, updateField);
            return db.ExecteNonQueryText(sql, ps.Concat(paramPS).ToArray()) > 0;
        }
        #endregion

        #region 查询
        /// <summary>
        /// 根据传入的实体判断数据是否已经存在（有主键时，排除主键所在记录）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="m"></param>
        /// <returns></returns>
        public bool IsExists(T m)
        {
            string sqlWhere = string.Join(" AND ", pros.Where(p =>
            {
                Type t = p.PropertyType;
                object value = p.GetValue(m, null);
                if (t.IsValueType)
                {
                    return value != null && !Activator.CreateInstance(t).Equals(value) && t != typeof(DateTime);
                }
                else
                {
                    return t == typeof(String) && value != null && value.ToString() != "";
                }
            }).Select(p => string.Format("[{0}]=@{0}", p.Name)));
            StringBuilder sql = new StringBuilder();
            if (string.IsNullOrEmpty(sqlWhere) == false)
            {
                sql.Append(" AND " + sqlWhere);
            }
            if (m.ID > 0)
            {
                sql.Append(" AND [ID]<>@ID");
            }
            return db.Exists(ModelAttribute.GetTableName<T>(), sql.ToString(), ADOHelper.SetParmValues(m, m.ID > 0));
        }
        /// <summary>
        /// 根据传入的实体数据和条件字段名判断是否已经存在（有主键时，排除主键所在记录）
        /// 查询条件string[]子元素的交集（AND）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="m"></param>
        /// <param name="listStrs">条件字段名数组</param>
        /// <returns></returns>
        public bool IsExists(T m, params string[] listStrs)
        {
            StringBuilder sql = new StringBuilder();
            if (listStrs.Length > 0)
            {
                sql.Append(" AND " + string.Join(" AND ", pros.Where(p => listStrs[0].Contains(p.Name)).Select(p => string.Format("[{0}]=@{0}", p.Name))));
            }
            if (m.ID > 0)
            {
                sql.Append(" AND [ID]<>@ID");
            }
            return db.Exists(ModelAttribute.GetTableName<T>(), sql.ToString(), ADOHelper.SetParmValues(m, m.ID > 0));
        }
        /// <summary>
        /// 根据传入的实体数据和条件字段名判断是否已经存在（有主键时，排除主键所在记录）
        /// 查询条件为List子元素的交集（AND），string[]子元素的并集（OR）
        /// 查询条件字符串必须是实体中存在的属性名称
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="m"></param>
        /// <param name="listStrs">条件字段名数组</param>
        /// <returns></returns>
        public bool IsExists(T m, List<string[]> listStrs)
        {
            StringBuilder sql = new StringBuilder();
            if (listStrs != null)
            {
                if (listStrs.Count == 1)
                    sql.Append(" AND " + string.Join(" AND ", pros.Where(p => listStrs[0].Contains(p.Name)).Select(p => string.Format("[{0}]=@{0}", p.Name))));
                else
                    sql.Append(" AND ((" + string.Join(") OR (", listStrs.Select(strs => string.Join(" AND ", pros.Where(p => strs.Contains(p.Name)).Select(p => string.Format("[{0}]=@{0}", p.Name))))) + "))");
            }
            if (m.ID > 0)
            {
                sql.Append(" AND [ID]<>@ID");
            }
            return db.Exists(TableName, sql.ToString(), ADOHelper.SetParmValues(m, m.ID > 0));
        }
        public T GetModel(int id)
        {
            string sql = string.Format(@"SELECT TOP 1 {0} FROM [{1}] WHERE [{2}]=@{2}", string.Join(",", pros.Select(p => string.Format("[{0}]", p.Name))), TableName, PKName);
            DbParameter[] ps = new DbParameter[] { DBHelpers.CreateParameter(PKName, id) };
            return db.GetModelText<T>(sql, ps);
        }

        #endregion
    }
}
